// .........................................................................
// Title: holdings_sumstats.do
//
// Generates summary statistics for the overall holdings of insurers as well
// as mutual funds and ETFs in the sample
// .........................................................................

* --------------------------------------
* Holdings summary statistics: insurers
* --------------------------------------

* Import total portfolio summary data from S&P
foreach asset in "LtBonds" "StBonds" "PrefStock" "CommStock" {

    di "Processing `asset'"
    import excel using "$raw/sp_insurance/additional/PortfolioSummary-`asset'.xls", clear
    drop if _n < 4
    foreach var of varlist * {
        local try = `var'[1]
        cap rename `var' value_`try'
    }
    rename value_ entity_name
    rename B entity_key
    drop if _n < 3
    reshape long value_, i(entity_name entity_key) j(year) string
    replace year = subinstr(year, "Y", "", .)
    rename value_ value
    destring value year, force replace
    gen asset = "`asset'"
    save "$tmp/sp_portfolio_totals_`asset'", replace

}

* Append asset classes
clear
foreach asset in "LtBonds" "StBonds" "PrefStock" "CommStock" {
    append using "$tmp/sp_portfolio_totals_`asset'"
}
rename value value_
reshape wide value_, i(entity_name entity_key year) j(asset) string
save "$tmp/sp_portfolio_totals", replace

* Process portfolio totals
use "$tmp/sp_portfolio_totals", clear
drop if year < 2005
drop if missing(value_CommStock) & missing(value_LtBonds) & missing(value_PrefStock) & missing(value_StBonds)
foreach var of varlist value* {
    replace `var' = 0 if missing(`var')
}
gen value_Tot = value_CommStock + value_LtBonds + value_PrefStock + value_StBonds
foreach var of varlist value* {
    replace `var' = `var' / 1e6
}
drop if value_Tot < 2.5e-2 // 25M threshold
replace value_Tot = value_Tot * 1e3
gen number = 1

* Market shares
gsort year
by year: egen totVal = total(value_Tot)
gen market_share = value_Tot / totVal
gsort year -value_Tot
by year: gen size_rank = _n
by year: gen cum_market_share = sum(market_share)

* Concentration ratios
gen _cr10 = cum_market_share * 100 if size_rank == 10
gen _cr50 = cum_market_share * 100 if size_rank == 50
gen _cr100 = cum_market_share * 100 if size_rank == 100

* Collapse to get sumstats
collapse (sum) number (mean) size_mean=value_Tot (p50) size_p50=value_Tot (p95) size_p95=value_Tot (sd) size_sd=value_Tot ///
    (max) cr10=_cr10 cr50=_cr50 cr100=_cr100, by(year)
foreach var of varlist size_* cr* {
    replace `var' = round(`var')
}
keep if inlist(year, 2005, 2010, 2015, 2020)
gen panel = "A. US Insurers"
order panel
save "$tmp/holdings_sumstats_insurers", replace

* --------------------------------------
* Holdings summary statistics: all funds
* --------------------------------------

* all funds
foreach year in 2005 2010 2015 2020 {

    di "Processing `year'"
    use "$morningstar_hd/HD_`year'_y.dta", clear
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu1)
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu2)
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu3)
    keep if inlist(DomicileCountryId, "USA", "EMU", "CAN", "GBR", "SWE", "NOR", "DNK", "CHE", "AUS")
    gen marketvalue_usd = marketvalue / lcu_per_usd_eop
    replace marketvalue_usd = marketvalue / 1e9
    gcollapse (sum) marketvalue_usd, by(MasterPortfolioId DomicileCountryId)

    drop if marketvalue_usd < 2.5e-2
    gen number = 1
    gen region = "us" if DomicileCountryId == "USA"
    replace region = "foreign" if DomicileCountryId != "USA"
    gsort region
    by region: egen totVal = total(marketvalue_usd)
    gen market_share = marketvalue_usd / totVal
    gsort region -marketvalue_usd
    by region: gen size_rank = _n
    by region: gen cum_market_share = sum(market_share)
    gen _cr10 = cum_market_share if size_rank == 10
    gen _cr50 = cum_market_share if size_rank == 50
    gen _cr100 = cum_market_share if size_rank == 100
    gen year = `year'

    collapse (sum) number (mean) size_mean=marketvalue_usd (p50) size_p50=marketvalue_usd (p95) size_p95=marketvalue_usd (sd) size_sd=marketvalue_usd ///
        (max) cr10=_cr10 cr50=_cr50 cr100=_cr100, by(year region)
    gen sector_total = size_mean * number
    save "$tmp/all_funds_sumstats_`year'", replace
    
}

* append years
clear
foreach year in 2005 2010 2015 2020 {
    append using "$tmp/all_funds_sumstats_`year'"
}
foreach var of varlist size* {
    replace `var' = round(`var' * 1e3)
}
foreach var of varlist cr* {
    replace `var' = round(`var' * 100)
}
replace sector_total = round(sector_total)
sort region year

keep if inlist(year, 2005, 2010, 2015, 2020)
gen panel = "B. US Funds (All)" if region == "us"
replace panel = "C. Non-US Funds (All)" if region == "foreign"
order panel
drop region sector_total
save "$tmp/holdings_sumstats_all_funds", replace

* ---------------------------------------------
* Holdings summary statistics: specialist funds
* ---------------------------------------------

* fund categorization; manually fix some misclassifications
use "$output/morningstar_mapping/morningstar_mapping_uniqueonly.dta", clear
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 1976348 // BankInvest Korte HY Obligationer
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 1975485 // Fidelity SAI Total Bond Fund
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 2032829 // SEB Företagsobligationsfond Hållbar
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 2121494 // SEB FRN Fond Hållbar
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 1975003 // Strategic Advisers® Fidelity® Core IncFd
replace BroadCategoryGroup = "Fixed Income" if MasterPortfolioId == 2119234 // Fidelity® Series Bond Index Fund
save "$tmp/morningstar_api_data_unique_adj", replace

* specialist funds
foreach year in 2005 2010 2015 2020 {

    di "Processing `year'"
    use "$morningstar_hd/HD_`year'_y.dta", clear
    mmerge MasterPortfolioId using "$tmp/morningstar_api_data_unique_adj", unmatched(m)
    drop _merge
    keep if BroadCategoryGroup == "Fixed Income"
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu1)
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu2)
    replace DomicileCountryId = "EMU" if inlist(DomicileCountryId, $eu3)
    keep if inlist(DomicileCountryId, "USA", "EMU", "CAN", "GBR", "SWE", "NOR", "DNK", "CHE", "AUS")

    gen marketvalue_usd = marketvalue / lcu_per_usd_eop
    replace marketvalue_usd = marketvalue / 1e9
    gcollapse (sum) marketvalue_usd, by(MasterPortfolioId DomicileCountryId cusip)
    save "$tmp/specialist_fund_holdings_`year'", replace
    gcollapse (sum) marketvalue_usd, by(MasterPortfolioId DomicileCountryId)
    drop if marketvalue_usd < 2.5e-2
    gen number = 1

    gen region = "us" if DomicileCountryId == "USA"
    replace region = "foreign" if DomicileCountryId != "USA"
    gsort region
    by region: egen totVal = total(marketvalue_usd)
    gen market_share = marketvalue_usd / totVal
    gsort region -marketvalue_usd
    by region: gen size_rank = _n
    by region: gen cum_market_share = sum(market_share)

    gen _cr10 = cum_market_share if size_rank == 10
    gen _cr50 = cum_market_share if size_rank == 50
    gen _cr100 = cum_market_share if size_rank == 100
    gen year = `year'

    collapse (sum) number (mean) size_mean=marketvalue_usd (p50) size_p50=marketvalue_usd (p95) size_p95=marketvalue_usd (sd) size_sd=marketvalue_usd ///
        (max) cr10=_cr10 cr50=_cr50 cr100=_cr100, by(year region)
    gen sector_total = size_mean * number
    save "$tmp/specialist_funds_sumstats_`year'_fi", replace
    
}

* append years
clear
foreach year in 2005 2010 2015 2020 {
    append using "$tmp/specialist_funds_sumstats_`year'_fi"
}
foreach var of varlist size* {
    replace `var' = round(`var' * 1e3)
}
foreach var of varlist cr* {
    replace `var' = round(`var' * 100)
}
replace sector_total = round(sector_total)
sort region year

keep if inlist(year, 2005, 2010, 2015, 2020)
gen panel = "D. US Funds (Specialists)" if region == "us"
replace panel = "E. Non-US Funds (Specialists)" if region == "foreign"
order panel
drop region sector_total
save "$tmp/holdings_sumstats_specialist_funds", replace

* ----------------------------------------------------
* Construct full holdings data sumstat table (Table 1)
* ----------------------------------------------------
clear
append using "$tmp/holdings_sumstats_insurers"
append using "$tmp/holdings_sumstats_all_funds"
append using "$tmp/holdings_sumstats_specialist_funds"
sort panel year
rename panel Panel
rename year Year
rename number N
rename size_mean Assets_Mean
rename size_p50 Assets_Median
rename size_p95 Assets_P95
rename size_sd Assets_SD
rename cr10 CR10
rename cr50 CR50
rename cr100 CR100
save "$tables/holdings_overview.dta", replace
export delimited "$tables/holdings_overview.txt", noquote delimiter(tab) replace
